-- created by kumiler
-- on 2022/7/26 9:17
-- DESC 车件分离上一站装车聚合表ddl
-- modified by Jimmyk.ji
-- on 2023/7/10
-- DESC 增加罚款相关配置信息

set spark.sql.crossJoin.enabled=true;

insert overwrite table jms_dm.dm_waybill_transfer_separated_load_agg partition (dt)
select date_time,
       ordersource_code,
       ordersource_name,
       goods_type_code,
       goods_type_name,
       load_regional_id,
       load_regional_desc,
       load_agent_code,
       load_agent_name,
       load_franchisee_code,
       load_franchisee_name,
       load_station_code,
       load_station_name,
       load_station_type,
       diff_shipment_cnt,
       bill_separated_cnt,
       round(diff_shipment_cnt * 100 / sum(diff_shipment_cnt) over (), 5)   as diff_shipment_ratio,
       round(bill_separated_cnt * 100 / sum(bill_separated_cnt) over (), 5) as bill_separated_ratio,
       date_format(`current_timestamp`(), 'yyyy-MM-dd HH:mm:ss')            as insert_etl_time,
       center_load
       ,b.fee_name
       ,b.fee_level
       ,b.fee_rate_level
       ,b.fee_per
       ,b.fee_maximum
       ,b.fee_minimum
       ,b.fee_update_time
       ,b.fee_update_by_name
       ,b.fee_update_by_code
       ,net.agent_id    as  load_agent_id
       ,net.id          as  load_station_id
       ,t.dt
from (
         select date_time,
                ordersource_code,
                ordersource_name,
                goods_type_code,
                goods_type_name,
                load_regional_id,
                load_regional_desc,
                load_agent_code,
                load_agent_name,
                load_franchisee_code,
                load_franchisee_name,
                load_station_code,
                load_station_name,
                load_station_type,
                center_load,
                dt,
                sum(nvl(diff_shipment, 0))  as diff_shipment_cnt, -- 装车任务号不一致票数
                sum(nvl(bill_separated, 0)) as bill_separated_cnt -- 车件分离票数
         from jms_dm.dm_waybill_transfer_separated_detail_dt
         where dt = date_add('{{ execution_date | cst_ds }}',-1)
         group by date_time, load_regional_id, ordersource_code, ordersource_name,
                  goods_type_code, goods_type_name, load_regional_desc,
                  load_agent_code, load_agent_name,
                  load_franchisee_code, load_franchisee_name,
                  load_station_code, load_station_name,load_station_type, date_time, center_load,dt
     ) t
     left join (
          select  fee_name         as  fee_name
                 ,fee_level        as  fee_level
                 ,fee_rate_level   as  fee_rate_level
                 ,fee_per          as  fee_per
                 ,maximum          as  fee_maximum
                 ,minimum          as  fee_minimum
                 ,update_time      as  fee_update_time
                 ,update_by_name   as  fee_update_by_name
                 ,update_by_code   as  fee_update_by_code
                 ,'{{ execution_date | date_add(-1) | cst_ds }}'  as dt_b
            from jms_dim.dim_tab_forfeit_config_new
           where fee_name ='车件分离罚款' and  fee_start_time<= date_add('{{ execution_date | cst_ds }}',-1)
             and fee_end_time >= date_add('{{ execution_date | cst_ds }}',-1)
             and statue =1 and is_delete = 1
            order by fee_update_time desc limit 1
           ) b
     on t.dt = b.dt_b
     left join jms_dim.dim_network_whole_massage net
       on t.load_station_code = net.code
distribute by 1;
